* Author: Joe Tatarka and Ashley Pandya
* Name: 1_tables_and_figures.do
* Purpose: Recreate the tables and figures for "The Curious Surge of Productivity in U.S. Restaurants" NBER Working Paper
** The paper-ready versions of Figures 1, A1, and A3 were generated using excel (file provided), they are replicated here for continuity. Please note that some of the figures are produced out of order.

* Set Global File Paths [INSERT YOUR PACKAGE PATH HERE]
global root = "T:/service_industries/replication_package"

global raw_root = "${root}/datasets/raw"
global intermediate_root = "${root}/datasets/intermediate"
global built_root = "${root}/datasets/built"
global exhibits_root = "${root}/exhibits"
global bnw_root = "${exhibits_root}/bnw"

graph set window fontface "Arial"

* ---------------------------------------------------------------------------- *
*                               MAIN TEXT
* ---------------------------------------------------------------------------- *

*******************************************************************************************
*** Figure 1 - Real Sales per Emp, Food Services and Drinking Places, Seasonally Adjusted
*******************************************************************************************

*** The paper uses the Excel-generated figure; it is recreated here.

use "${built_root}/aggregates_build.dta", clear 

graph twoway line ann_real_sales_pr_emp_food_sa year_month_gs, ytitle("") yline(100, lcolor(black) lwidth(medthick))
graph export "${exhibits_root}/figure_1.pdf", replace

graph save f1.gph, replace


********************************************************************************
*** Figure 2 - Compare Cenus/BLS Sales and Employees to SafeGraph
********************************************************************************

use "${built_root}/aggregates_build.dta", clear 

keep if year_month_gs >= monthly("jan2019", "MY") & year_month_gs <= monthly("dec2022", "MY")

sum spend_sg_idx2019
gen high = r(max)
sum emp_sg_idx2019
gen low = r(min)

label var spend_limited_idx2019 "Census Sales"
label var spend_sg_idx2019 "SafeGraph Sales"
label var emp_limited_idx2019 "BLS Employees"
label var emp_sg_idx2019 "SafeGraph Employees"


*** Figure 2a
graph twoway ///
	line spend_limited_idx2019 year_month_gs, lcolor(stc1) lwidth(thick) ///
 || line spend_sg_idx2019 year_month_gs, lcolor(stc2) lwidth(thick) lpattern(dash) ///
    title("") subtitle("") ytitle("") ///
	legend(order(1 2) cols(2) position(6)) ////
	yline(100, lcolor(black) lwidth(thin)) ///
	xscale(range(708 758)) ylabel(60(10)120)
graph export "${exhibits_root}/figure_2a.pdf", replace 


*** Figure 2b
graph twoway rarea high low year_month_gs if year_month_gs >= 748, lcolor(gs14%0) fcolor(gs14%50) ///
	|| line emp_limited_idx2019 year_month_gs, lcolor(stc1) lwidth(thick) ///
	|| line emp_sg_idx2019 year_month_gs if year_month_gs <= 747, lcolor(stc2) lwidth(thick) lpattern(dash) ///
	|| line emp_sg_idx2019 year_month_gs if year_month_gs >= 748 , lcolor(stc2) lwidth(thick) lpattern(dash) ///
	title("") subtitle("") ytitle("") ///
	legend(order(2 3) cols(2) position(6)) ///
	yline(100, lcolor(black) lwidth(thin)) ///
	xscale(range(708 758)) ylabel(60(10)120)
graph export "${exhibits_root}/figure_2b.pdf", replace 

*********************************************************************************
**** Figure 3, Average SafeGraph Employment per Restaurant 
*********************************************************************************

use "${built_root}/main_build.dta", clear

* Take monthly average of employment measure 
collapse (mean) spend visits emp, by(year_month_gs)
label var year_month_gs "Year-Month"

** create index to avg 2019 
sort year_month_gs
foreach var in "spend" "visits" "emp" {
	egen `var'_2019 = mean(`var') if year_month_gs <= monthly("dec2019", "MY")
	gen `var'_index = 100*(`var'/`var'_2019[1])
}

label var emp_index "Average Employee Shifts"

gen high =  120
gen low = 60

graph twoway rarea high low year_month_gs if year_month_gs >= 748, lcolor(gs14%0) fcolor(gs14%50) || line emp_index year_month_gs if year_month_gs <=747, lcolor(stc1) || line emp_index year_month_gs if year_month_gs >= 748, lcolor(stc1) ytitle("") legend(off) yline(100, lcolor(black) lwidth(thin)) xscale(range(708 758)) ylabel(60(10)120) 
graph export "${exhibits_root}/figure_3.pdf", replace

********************************************************************************
**** Figure 4, Average Customer Dwell Time Visit Shares
********************************************************************************

use "${built_root}/main_build.dta", clear

** Take Monthly Average of Dwell Shares
collapse (mean) dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share dwell_61_to_240_share, by(year_month_gs)

label var dwell_0_to_10_share "0-10 Minutes"
label var dwell_11_to_20_share "11-20 Minutes"
label var dwell_21_to_60_share "21-60 Minutes"
label var dwell_61_to_240_share "61-240 Minutes"
label var year_month_gs "Year-Month"

gen high = 0.7
gen low = 0

graph twoway rarea high low year_month_gs if year_month_gs >=748, lcolor(gs14%0) fcolor(gs14%50) ///
	|| line dwell_0_to_10_share year_month_gs if year_month_gs <= 747, lcolor(stc1) lwidth(thick) ///
	|| line dwell_0_to_10_share year_month_gs if year_month_gs >= 748, lcolor(stc1) lwidth(thick) ///
	|| line dwell_11_to_20_share year_month_gs if year_month_gs <= 747, lcolor(stc2) lpattern(dash_dot) lwidth(thick) ///
	|| line dwell_11_to_20_share year_month_gs if year_month_gs >= 748, lcolor(stc2) lpattern(dash_dot) lwidth(thick) ///
	|| line dwell_21_to_60_share year_month_gs if year_month_gs <= 747, lcolor(stc3) lpattern(longdash) lwidth(thick) ///
	|| line dwell_21_to_60_share year_month_gs if year_month_gs >= 748, lcolor(stc3) lpattern(longdash) lwidth(thick) ///
	|| line dwell_61_to_240_share year_month_gs if year_month_gs <= 747, lcolor(stc4) lpattern(shortdash) lwidth(thick) ///
	|| line dwell_61_to_240_share year_month_gs if year_month_gs >= 748, lcolor(stc4) lpattern(shortdash) lwidth(thick) ///
	title("") ytitle("Share of Visits") ylabel(0(0.1)0.7, format(%3.1fc)) ///
	legend(order(2 4 6 8) cols(2) position(6))  ///
	xscale(range(708 758))
graph export "${exhibits_root}/figure_4.pdf", replace 


* ---------------------------------------------------------------------------- *
*                                   APPENDIX
* ---------------------------------------------------------------------------- *

********************************************************************************
**** Table 1, Table A1, Figure A5, Figure A6
**** Dwell Time Changes and Productivity Regressions 
********************************************************************************

*** Easier to make all of these tables and figures in one fell swoop

use "${built_root}/main_build.dta", clear

*** Shorten Brand Names
replace brands = "Domino's" if brands == "Domino's Pizza"
replace brands = "Chipotle" if brands == "Chipotle Mexican Grill"

*** Create Indicator Variables for all brands + entire sample
gen brands_1 = 1
label var brands_1 "All Restaurants"
local i = 2
foreach brands in  "McDonald's" "Chick-fil-A" "Taco Bell" "Wendy's" "Burger King" "Starbucks" "Dunkin'" "Subway" "Chipotle" "Domino's" "Pizza Hut"  {
	gen brands_`i' = (brands == "`brands'")
	label var brands_`i' "`brands'"
	local i = `i' + 1
}

*** Create frame with the change in avg dwell shares 
*** create like 48 vars of dwell share * brand dummy and then collapse to year_month 
*** Use this same frame throughout the mega loop

frame copy default new, replace

frame new {
	foreach var of varlist brands_1-brands_12 {
		foreach dwell_var of varlist dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share dwell_61_to_240_share {
			gen `var'_`dwell_var' = `dwell_var' if `var' == 1
		}
	}
	
	collapse (mean) brands_1_dwell_0_to_10_share - brands_12_dwell_61_to_240_share, by(year_month_gs)
}

label var dwell_0_to_10_share "0-10 Minutes"
label var dwell_11_to_20_share "11-20 Minutes"
label var dwell_21_to_60_share "21-60 Minutes"


**** REGRESSION TIME 
** Loop through and run regression for each restaurant chain + entire sample
** Basically: run regression, multiply regression coefficients by the change in dwell shares to create implied productivity change, and then continue to next restaurant chain

est clear
foreach num of numlist 1/12 {
	local reg_title_`num': var label brands_`num'
	* regression
	quietly: eststo, title("`reg_title_`num''"): reghdfe ln_spend_prod dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share if brands_`num' == 1, absorb(placekey year_month_gs) vce(cluster placekey)
		
	frame change new 
	gen brands_`num'_diff = 0
	gen brands_`num'_lb = 0 
	gen brands_`num'_ub = 0
	
	foreach dwell in "dwell_0_to_10" "dwell_11_to_20" "dwell_21_to_60" "dwell_61_to_240" {
		quietly: sum brands_`num'_`dwell'_share if year_month_gs <= monthly("dec2019", "MY")
		local `dwell'_2019 = r(mean)
	}
	
	quietly: foreach n of numlist 1(1)48 {
		local 0_10_diff = brands_`num'_dwell_0_to_10_share[`n'] - `dwell_0_to_10_2019'
		local 11_20_diff = brands_`num'_dwell_11_to_20_share[`n'] - `dwell_11_to_20_2019' 
		local 21_60_diff = brands_`num'_dwell_21_to_60_share[`n'] - `dwell_21_to_60_2019'
	
		* Multiply coefficients by change from 2019 avg
		lincom `0_10_diff'*dwell_0_to_10_share + `11_20_diff'*dwell_11_to_20_share + `21_60_diff'*dwell_21_to_60_share
		replace brands_`num'_diff = r(estimate) if _n == `n'
		replace brands_`num'_lb = r(lb) if _n == `n'
		replace brands_`num'_ub = r(ub) if _n == `n'
	}

	*** Estadd Some Locals (Change from 2019 avg to April 2022)
	foreach dwell in "dwell_0_to_10" "dwell_11_to_20" "dwell_21_to_60" "dwell_61_to_240" {
		estadd scalar `dwell'_change = brands_`num'_`dwell'_share[40] - ``dwell'_2019'
	}
	estadd scalar projected_prod =  100*brands_`num'_diff[40]
	
	frame change default 
	
}


*** Table 1, Regression Table
** We do some manual editing after to make the tables look nicer for the paper
esttab est1 est2 est3 est4 est5 est6 using "${exhibits_root}/table_1.rtf", nostar onecell mtitle se noomitted nocons nobase compress stats(N r2, label("N" "R-Squared") fmt(%9.0fc 2)) label replace title("Table 1") nonote collabel(none)

esttab est1 est2 est3 est4 est5 est6 using "${exhibits_root}/table_1.rtf", nostar nomtitle nonum se drop(_cons dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share) compress stats( dwell_0_to_10_change dwell_11_to_20_change dwell_21_to_60_change dwell_61_to_240_change projected_prod, label("0-10 Minutes" "11-20 Minutes" "21-60 Minutes" "61-240 Minutes" "Impl. Change % Productivity") fmt(3 3 3 3 1)) label append  title("Average dwell time category share change from 2019 to April 2022 and implied productivity change:")


*** Table A1, Regression Table - Additional Restaurant Chains
** We do some manual editing after to make the tables look nicer for the paper
esttab est7 est8 est9 est10 est11 est12 using "${exhibits_root}/table_A1.rtf", nostar onecell mtitle se noomitted nocons nobase compress stats(N r2, label("N" "R-Squared") fmt(%9.0fc 2)) label replace title("Table A1") nonote collabel(none)

esttab est7 est8 est9 est10 est11 est12 using "${exhibits_root}/table_A1.rtf", nostar nomtitle nonum se drop(_cons dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share) compress stats( dwell_0_to_10_change dwell_11_to_20_change dwell_21_to_60_change dwell_61_to_240_change projected_prod, label("0-10 Minutes" "11-20 Minutes" "21-60 Minutes" "61-240 Minutes" "Impl. Change % Productivity") fmt(3 3 3 3 1)) label append  title("Average dwell time category share change from 2019 to April 2022 and implied productivity change:") 


**** Figure A5, Productivity Projections 
********************************************

frame new {

	gen high = 0.5
	gen low = -0.1
	
	foreach num of numlist 1/6 {
		label var brands_`num'_diff "`reg_title_`num''"
	}
	
	label var year_month_gs "Year-Month"
	graph twoway rarea high low year_month_gs if year_month_gs >= 748, lcolor(gs14%0) fcolor(gs14%50) || line brands_1_diff brands_2_diff brands_3_diff brands_4_diff brands_5_diff brands_6_diff year_month_gs if year_month_gs <=747, lcolor(black stc1 stc2 stc3 stc4 stc5) lwidth(thick) || line brands_1_diff brands_2_diff brands_3_diff brands_4_diff brands_5_diff brands_6_diff year_month_gs if year_month_gs >=748, lcolor(black stc1 stc2 stc3 stc4 stc5) lwidth(thick) yline(0, lcolor(black) lwidth(thin)) title("") ylabel(-0.1(0.1)0.5, format(%3.1fc)) ytitle("Productivity Difference, Log Scale") legend(order(6 7 5 3 4 2)) 
	graph export "${exhibits_root}/figure_A5.pdf", replace
}

**** Figure A6, Productivity Projections - Additional Restaurant Chains 
**************************************************************************

frame new {
	
	foreach num of numlist 7/12 {
		label var brands_`num'_diff "`reg_title_`num''"
	}
	
	label var year_month_gs "Year-Month"
	graph twoway rarea high low year_month_gs if year_month_gs >= 748, lcolor(gs14%0) fcolor(gs14%50) || line brands_7_diff brands_8_diff brands_9_diff brands_10_diff brands_11_diff brands_12_diff year_month_gs if year_month_gs <=747, lcolor(stc6 stc7 stc8 stc9 stc10 stc11)  || line brands_7_diff brands_8_diff brands_9_diff brands_10_diff brands_11_diff brands_12_diff year_month_gs if year_month_gs >=748, lcolor(stc6 stc7 stc8 stc9 stc10 stc11) yline(0, lcolor(black) lwidth(thin)) title("") ylabel(-0.1(0.1)0.5, format(%3.1fc)) ytitle("Productivity Difference, Log Scale") legend(order(2 5 3 7 6 4)) 
	graph export "${exhibits_root}/figure_A6.pdf", replace
}


*******************************************************************************
***** Figure A1 - Real Sales per Emp, Food Services and Drinking Places and Limited Service, Not Seasonally Adjusted
*******************************************************************************

*** The paper uses the Excel-generated figure; it is recreated here.

use "${built_root}/aggregates_build.dta", clear 

label var prod_food_nsa_idx1992 "Total Industry"
label var prod_limited_food_cpi_idx1992 "Limited Service"

graph twoway line prod_food_nsa_idx1992 prod_limited_food_cpi_idx1992 year_month_gs, ytitle("") legend(order(1 2) cols(2) position(6)) yline(100, lcolor(black) lwidth(thin)) ylabel(80(10)140) xlabel(384(48)780) 
graph export "${exhibits_root}/figure_A1.pdf", replace 

*******************************************************************************
***** Figure A2 - Compare Census/BLS Productivity to SafeGraph Productivity
*******************************************************************************

use "${built_root}/aggregates_build.dta", clear 

keep if year_month_gs >= monthly("jan2019", "MY") & year_month_gs <= monthly("dec2022", "MY")

gen high = 140 
gen low = 80

label var prod_limited_idx2019 "Census/BLS Spend Productivity"
label var prod_sg_idx2019  "SafeGraph Spend Productivity"
label var visits_prod_sg_idx2019 "SafeGraph Visits Productivity"

graph twoway rarea high low year_month_gs if year_month_gs >=748, lcolor(gs14%0) fcolor(gs14%50) || line prod_limited_idx2019 year_month_gs, lcolor(stc1) lwidth(thick) || line prod_sg_idx2019 year_month_gs if year_month_gs <= 747, lcolor(stc2) || line prod_sg_idx2019 year_month_gs if year_month_gs >= 748, lcolor(stc2) || line visits_prod_sg_idx2019 year_month_gs if year_month_gs <= 747, lcolor(stc3) || line visits_prod_sg_idx2019 year_month_gs if year_month_gs >= 748, lcolor(stc3) title("") subtitle("") ytitle("") legend(order(2 3 5) cols(2) position(6)) yline(100, lcolor(black) lwidth(thin)) xscale(range(708 758))  ylabel(80(10)140)
graph export "${exhibits_root}/figure_A2.pdf", replace 

*/
*******************************************************************************
**** Figure A3, Total Capital Stock and Capital-per-Worker Indexes
********************************************************************************

*** The paper uses the Excel-generated figure; it is recreated here.

use "${built_root}/cap_build.dta", clear 

graph twoway ///
	(line kidx year, lpattern(solid) lcolor(blue) lwidth(medthick)) (line klidx year, lpattern(solid) lcolor(orange) lwidth(medthick)), ytitle("") xtitle("Year", size(small)) ylabel(0(20)180, labsize(vsmall)) xlabel(1992(1)2024, labsize(vsmall) angle(45)) legend(order(1 "Industry Total Capital Stock Index" 2 "Industry Capital per Worker Index") position(6) size(vsmall))
graph export "${exhibits_root}/figure_A3.pdf", replace


*******************************************************************************
**** Figure A4, Delivery and Driver App Usage, Aggregate Minutes per Day
********************************************************************************
use "${built_root}/gws_build.dta", clear

label var delivery_use_per_day_idx2019 "Delivery Apps"
label var driver_use_per_day_idx2019 "Driver Apps"

graph twoway line delivery_use_per_day_idx2019 driver_use_per_day_idx2019 year_month_gs, title("") ytitle("") yline(100, lcolor(black) lwidth(thin)) legend(pos(6) col(2) label(1 "Delivery Apps") label(2 "Driver Apps")) xlabel(708(12)768) xscale(range(708 770))
graph export "${exhibits_root}/figure_A4.pdf", replace


********************************************************************************************************************************
**** Figure A7, Implied Productivity Difference from Dwell Time Changes, Productivity Measured as Customer Visits per Employee 
********************************************************************************************************************************

use "${built_root}/main_build.dta", clear

*** Shorten Brand Names
replace brands = "Domino's" if brands == "Domino's Pizza"
replace brands = "Chipotle" if brands == "Chipotle Mexican Grill"

*** Create Indicator Variables for restaurant chains + entire sample
gen brands_1 = 1
label var brands_1 "All Restaurants"
local i = 2
foreach brands in  "McDonald's" "Chick-fil-A" "Taco Bell" "Wendy's" "Burger King" "Starbucks" "Dunkin'" "Subway" "Chipotle" "Domino's" "Pizza Hut"  {
	gen brands_`i' = (brands == "`brands'")
	label var brands_`i' "`brands'"
	local i = `i' + 1
}

frame copy default new, replace

frame new {
	foreach var of varlist brands_1-brands_12 {
		foreach dwell_var of varlist dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share dwell_61_to_240_share {
			gen `var'_`dwell_var' = `dwell_var' if `var' == 1
		}
	}
	
	collapse (mean) brands_1_dwell_0_to_10_share - brands_12_dwell_61_to_240_share, by(year_month_gs)
}

label var dwell_0_to_10_share "0-10 Minutes"
label var dwell_11_to_20_share "11-20 Minutes"
label var dwell_21_to_60_share "21-60 Minutes"

**** REGRESSION TIME 
est clear
foreach num of numlist 1/6 {
	local reg_title_`num': var label brands_`num'
	quietly: eststo, title("`reg_title_`num''"): reghdfe ln_visits_prod dwell_0_to_10_share dwell_11_to_20_share dwell_21_to_60_share if brands_`num' == 1, absorb(placekey year_month_gs) vce(cluster placekey)
		
	frame change new 
	
	gen brands_`num'_diff = 0
	gen brands_`num'_lb = 0 
	gen brands_`num'_ub = 0
	
	foreach dwell in "dwell_0_to_10" "dwell_11_to_20" "dwell_21_to_60" "dwell_61_to_240" {
		quietly: sum brands_`num'_`dwell'_share if year_month_gs <= monthly("dec2019", "MY")
		local `dwell'_2019 = r(mean)
	}
	
	quietly: foreach n of numlist 1(1)48 {
		local 0_10_diff = brands_`num'_dwell_0_to_10_share[`n'] - `dwell_0_to_10_2019'
		local 11_20_diff = brands_`num'_dwell_11_to_20_share[`n'] - `dwell_11_to_20_2019' 
		local 21_60_diff = brands_`num'_dwell_21_to_60_share[`n'] - `dwell_21_to_60_2019'
	
		lincom `0_10_diff'*dwell_0_to_10_share + `11_20_diff'*dwell_11_to_20_share + `21_60_diff'*dwell_21_to_60_share
		replace brands_`num'_diff = r(estimate) if _n == `n'
		replace brands_`num'_lb = r(lb) if _n == `n'
		replace brands_`num'_ub = r(ub) if _n == `n'
	}

	*** Estadd Some Locals (Change from 2019 avg to April 2022)
	foreach dwell in "dwell_0_to_10" "dwell_11_to_20" "dwell_21_to_60" "dwell_61_to_240" {
		estadd scalar `dwell'_change = brands_`num'_`dwell'_share[40] - ``dwell'_2019'
	}
	estadd scalar projected_prod =  100*brands_`num'_diff[40]
	
	frame change default 	
}

**** Figure A7
frame new {
	
	foreach num of numlist 1/6 {
		label var brands_`num'_diff "`reg_title_`num''"
	}
	gen high = 0.5
	gen low = -0.1
	
	label var year_month_gs "Year-Month"
	graph twoway rarea high low year_month_gs if year_month_gs >= 748, lcolor(gs14%0) fcolor(gs14%50) || line brands_1_diff brands_2_diff brands_3_diff brands_4_diff brands_5_diff brands_6_diff year_month_gs if year_month_gs <=747, lcolor(black stc1 stc2 stc3 stc4 stc5) lwidth(thick) || line brands_1_diff brands_2_diff brands_3_diff brands_4_diff brands_5_diff brands_6_diff year_month_gs if year_month_gs >=748, lcolor(black stc1 stc2 stc3 stc4 stc5) lwidth(thick) yline(0, lcolor(black) lwidth(thin)) title("") ylabel(-0.1(0.1)0.5, format(%3.1fc)) ytitle("Productivity Difference, Log Scale") legend(order(6 7 3 5 4 2)) 
	graph export "${exhibits_root}/figure_A7.pdf", replace
}
